package org.magicsoft.office.excel.impt;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.List;

import org.apache.commons.io.IOUtils;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipFile;
import org.docx4j.openpackaging.exceptions.Docx4JException;
import org.magicsoft.office.db.DatabaseType;
import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.docx4j.ExcelUtils;
import org.magicsoft.office.excel.model.File;
import org.magicsoft.office.excel.model.ResultItem;
import org.magicsoft.office.excel.model.ResultSummary;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.exception.DBException;
import org.magicsoft.office.exception.OfficeException;
import org.magicsoft.office.exception.UnsupportedException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelImport {

	private static final Logger logger = LoggerFactory.getLogger(ExcelImport.class);

	private int batchSize = 100;

	private boolean useTemporaryTable = true;

	private boolean dropTableAfterImport = true;

	private Connection conn;

	private DatabaseType databaseType;

	private ResultSummary resultSummary = new ResultSummary();

	private TempTableSQLBuilder tempTableSQLBuilder;

	private TempTableDataWriter tempTableDataWriter;

	private PrimaryKeyUpdater primaryKeyUpdater;

	private ForeignKeysUpdater foreignKeysUpdater;

	private RecordCompleter recordCompleter;

	private Validator validator;

	private ExistRecordsUpdater existRecordsUpdater;

	private NewRecordsInserter newRecordsInserter;

	private ParentIdUpdater parentIdUpdater;

	private ReportDataQuery reportDataQuery;

	private String tempFolder;

	public ExcelImport(Connection conn) {
		this.conn = conn;

		try {
			DatabaseMetaData dbmd = conn.getMetaData();
			String productName = dbmd.getDatabaseProductName();
			switch (productName) {
			case "Microsoft SQL Server":
				databaseType = DatabaseType.SQLSERVER;
				break;
			case "Oracle":
				databaseType = DatabaseType.ORACLE;
				break;
			default:
				throw new UnsupportedException();
			}
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * 设置jdbc批处理大小。
	 * 
	 * @param batchSize
	 *            一批提交的数据大小。
	 */
	public void setBatchSize(int batchSize) {
		this.batchSize = batchSize;
	}

	/**
	 * 使用物理表存储临时数据。<br />
	 * 一般情况下，没理由不使用临时表，请尽量只在debug的情况下使用它。
	 */
	public void usePhysicalTable() {
		this.useTemporaryTable = false;
	}

	/**
	 * 导入后保留临时表。<br />
	 * 一般情况下，没理由不删除，请尽量只在debug的情况下使用它。
	 */
	public void retainTableAfterImport() {
		this.dropTableAfterImport = false;
	}

	/**
	 * 导入文件集合。
	 * 
	 * @param files
	 *            file模型集合。
	 * @param is
	 *            输入流。这里的输入流只支持zip格式文件。
	 */
	public void importFiles(List<File> files, ZipFile zipFile) {
		this.resultSummary.setBeginDate(new Date());
		try {
			this.resultSummary.addFileCount(files.size());

			java.io.File tempFolderFile = new java.io.File(this.getTempFolder());
			if (!tempFolderFile.exists()) {
				tempFolderFile.mkdirs();
			}
			Enumeration<ZipEntry> entries = zipFile.getEntries();
			ZipEntry zipEntry;
			while (entries.hasMoreElements()) {
				zipEntry = entries.nextElement();

				IOUtils.copy(zipFile.getInputStream(zipEntry),
						new FileOutputStream(this.getTempFolder() + java.io.File.separator + zipEntry.getName()));
			}

			for (File file : files) {
				try (InputStream nis = new FileInputStream(
						this.getTempFolder() + java.io.File.separator + file.getFileName());) {
					this.importSheets(file.getFileName(), file.getSheets(), nis);
				}
			}
		} catch (IOException e) {
			throw new OfficeException(e);
		} finally {
			this.resultSummary.setEndDate(new Date());
		}
	}

	/**
	 * 导入单一的文件。
	 * 
	 * @param File
	 *            file模型。
	 * @param is
	 *            文件输入流。
	 */
	public void importFile(File file, InputStream is) {
		try {
			this.resultSummary.setBeginDate(new Date());
			this.resultSummary.addFileCount(1);
			this.importSheets(file.getFileName(), file.getSheets(), is);
		} finally {
			this.resultSummary.setEndDate(new Date());
		}
	}

	public void writeHtmlReport(Writer writer) {
		new HTMLReportGenerator().generate(this.resultSummary, writer);
	}

	/**
	 * 导入sheet集合。
	 * 
	 * @param sheets
	 *            sheet集合。
	 * @param is
	 *            输入流。
	 * @throws Docx4JException
	 */
	private void importSheets(String fileName, List<Sheet> sheets, InputStream is) {
		this.resultSummary.addSheetCount(sheets.size());

		int i = 0;
		ExcelUtils excelUtils = new ExcelUtils();
		excelUtils.openWorkbook(is);
		String insertSQL;
		ResultItem resultItem = null;
		for (Sheet sheet : sheets) {
			try {
				resultItem = new ResultItem(fileName, sheet.getSheetName());
				sheet.initTempTableName(this.databaseType, this.useTemporaryTable);

				insertSQL = this.getTempTableSQLBuilder().build(sheet, conn, databaseType, useTemporaryTable);
				this.getTempTableDataWriter().write(sheet, conn, i++, this.batchSize, excelUtils, insertSQL,
						databaseType);
				this.getPrimaryKeyUpdater().update(sheet, conn, databaseType);
				this.getForeignKeysUpdater().update(sheet, conn, databaseType);
				if (this.getRecordCompleter() != null) {
					this.getRecordCompleter().doComplete(sheet, conn, databaseType);
				}
				this.getValidator().check(sheet, conn, databaseType);
				this.getExistRecordsUpdater().update(sheet, conn, databaseType);
				this.getNewRecordsInserter().insert(sheet, conn);
				this.getParentIdUpdater().update(sheet, conn, databaseType);
				this.getReportDataQuery().doQuery(sheet, conn, resultItem);
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				if (resultItem != null) {
					resultItem.setInsertCount(0);
					resultItem.setUpdateCount(0);
					resultItem.setErrorMessge(e.getMessage());
				}
			} finally {
				this.resultSummary.addItem(resultItem);
				if (this.dropTableAfterImport) {
					DatabaseUtils.executeUpdate(this.conn, "drop table " + sheet.getTempTableName());
				}
			}
		}
	}

	public String getTempFolder() {
		if (this.tempFolder == null) {
			this.tempFolder = System.getProperty("user.home") + java.io.File.separator + "temp";
		}
		return this.tempFolder;
	}

	public void setTempFolder(String tempFolder) {
		this.tempFolder = tempFolder;
	}

	public TempTableSQLBuilder getTempTableSQLBuilder() {
		if (this.tempTableSQLBuilder == null) {
			this.tempTableSQLBuilder = new TempTableSQLBuilder();
		}
		return tempTableSQLBuilder;
	}

	public void setTempTableSQLBuilder(TempTableSQLBuilder tempTableSQLBuilder) {
		this.tempTableSQLBuilder = tempTableSQLBuilder;
	}

	public Validator getValidator() {
		if (this.validator == null) {
			this.validator = new Validator();
		}
		return this.validator;
	}

	public void setValidator(Validator validator) {
		this.validator = validator;
	}

	public TempTableDataWriter getTempTableDataWriter() {
		if (this.tempTableDataWriter == null) {
			this.tempTableDataWriter = new TempTableDataWriter();
		}
		return this.tempTableDataWriter;
	}

	public void setTempTableDataWriter(TempTableDataWriter tempTableDataWriter) {
		this.tempTableDataWriter = tempTableDataWriter;
	}

	public PrimaryKeyUpdater getPrimaryKeyUpdater() {
		if (this.primaryKeyUpdater == null) {
			this.primaryKeyUpdater = new PrimaryKeyUpdater();
		}
		return this.primaryKeyUpdater;
	}

	public void setPrimaryKeyUpdater(PrimaryKeyUpdater primaryKeyUpdater) {
		this.primaryKeyUpdater = primaryKeyUpdater;
	}

	public ForeignKeysUpdater getForeignKeysUpdater() {
		if (this.foreignKeysUpdater == null) {
			this.foreignKeysUpdater = new ForeignKeysUpdater();
		}
		return this.foreignKeysUpdater;
	}

	public void setForeignKeysUpdater(ForeignKeysUpdater foreignKeysUpdater) {
		this.foreignKeysUpdater = foreignKeysUpdater;
	}

	public RecordCompleter getRecordCompleter() {
		return this.recordCompleter;

	}

	public void setRecordCompleter(RecordCompleter recordCompleter) {
		this.recordCompleter = recordCompleter;
	}

	public ExistRecordsUpdater getExistRecordsUpdater() {
		if (this.existRecordsUpdater == null) {
			this.existRecordsUpdater = new ExistRecordsUpdater();
		}
		return this.existRecordsUpdater;
	}

	public void setExistRecordsUpdater(ExistRecordsUpdater existRecordsUpdater) {
		this.existRecordsUpdater = existRecordsUpdater;
	}

	public NewRecordsInserter getNewRecordsInserter() {
		if (this.newRecordsInserter == null) {
			this.newRecordsInserter = new NewRecordsInserter();
		}
		return this.newRecordsInserter;
	}

	public void setNewRecordsInserter(NewRecordsInserter newRecordsInserter) {
		this.newRecordsInserter = newRecordsInserter;
	}

	public ParentIdUpdater getParentIdUpdater() {
		if (this.parentIdUpdater == null) {
			this.parentIdUpdater = new ParentIdUpdater();
		}
		return this.parentIdUpdater;
	}

	public void setParentIdUpdater(ParentIdUpdater parentIdUpdater) {
		this.parentIdUpdater = parentIdUpdater;
	}

	public ReportDataQuery getReportDataQuery() {
		if (this.reportDataQuery == null) {
			this.reportDataQuery = new ReportDataQuery();
		}
		return this.reportDataQuery;
	}

	public void setReportDataQuery(ReportDataQuery reportDataQuery) {
		this.reportDataQuery = reportDataQuery;
	}

}
